---
sidebar_label: Query performance
sidebar_position: 11
description: Performance of Hasura GraphQL queries on Postgres
keywords:
  - hasura
  - docs
  - postgres
  - schema
  - queries
  - performance
---

import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# Postgres: Query Performance

## Introduction

Sometimes queries can become slow due to large data volumes or levels of nesting. This page explains how to identify the
query performance, and how queries can be optimized.

## Analyzing query performance {#pg-analyzing-query-performance}

Let's say we want to analyze the following query:

```graphql
query {
  authors(where: { name: { _eq: "Mario" } }) {
    rating
  }
}
```

In order to analyze the performance of a query, you can click on the `Analyze` button on the Hasura Console:

<Thumbnail src="/img/queries/analyze-query.png" alt="Query analyze button on Hasura Console" width="800px" />

The following query execution plan is generated:

<Thumbnail
  src="/img/queries/query-analysis-before-index.png"
  alt="Execution plan for Hasura GraphQL query"
  width="800px"
/>

We can see that a sequential scan is conducted on the `authors` table. This means that Postgres goes through every row
of the `authors` table in order to check if the author's name equals "Mario". The `cost` of a query is an arbitrary
number generated by Postgres and is to be interpreted as a measure of comparison rather than an absolute measure of
something.

Read more about query performance analysis in the
[Postgres explain statement docs](https://www.postgresql.org/docs/current/sql-explain.html).

## Query optimization

### Using PG indexes {#pg-data-validation-pg-indexes}

[Postgres indexes](https://www.tutorialspoint.com/postgresql/postgresql_indexes.htm) are special lookup tables that
Postgres can use to speed up data lookup. An index acts as a pointer to data in a table, and it works very similar to an
index in the back of a book. If you look in the index first, you'll find the data much quicker than searching the whole
book (or - in this case - database).

Let's say we know that `authors` table is frequently queried by `name`:

```graphql
query {
  authors(where: { name: { _eq: "Mario" } }) {
    rating
  }
}
```

We've seen in the [above example](#pg-analyzing-query-performance) that by default Postgres conducts a sequential scan
i.e. going through all the rows. Whenever there is a sequential scan, it can be optimized by adding an index.

The following statement sets an index on `name` in the `authors` table.

```plsql
CREATE INDEX ON authors (name);
```

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

An index can be added in the `Data -> SQL` tab in the Hasura Console.

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add your
create index statement to the `up.sql` file. Also, add an SQL statement to revert that statement to the `down.sql` file
in case you need to [roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the migration.

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can add an index by making an API call to the
[run_sql schema API](/api-reference/schema-api/run-sql.mdx#schema-run-sql):

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
   "type": "run_sql",
   "args": {
      "source": "<db_name>",
      "sql": "<create index statement>"
   }
}
```

</TabItem>
</Tabs>

Let's compare the performance analysis to [the one before adding the index](#pg-analyzing-query-performance). What was a
`sequential scan` in the example earlier is now an `index scan`. `Index scans` are usually more performant than
`sequential scans`. We can also see that the `cost` of the query is now lower than the one before we added the index.

<Thumbnail
  src="/img/queries/query-analysis-after-index.png"
  alt="Execution plan for Hasura GraphQL query"
  width="800px"
/>

:::info Note

In some cases sequential scans can still be faster than index scans, e.g. if the result returns a high percentage of the
rows in the table. Postgres comes up with multiple query plans and takes the call on what kind of scan would be faster.

:::
